﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace WindowsFormsApp1
{
    
    public partial class Form1 : Form
    {
        public EduBaseDemo edubase;
       public int classsno;
        public int courseno;
        public Form1()
        {
            edubase = new EduBaseDemo();
            InitializeComponent();
            load();
        }
        public void load()
        {
            this.treeView1.Nodes.Clear();   
            var departments = edubase.tb_Department.ToList();
            foreach ( var department in departments )
            {
               var departmentnode=new TreeNode(department.Name);
                this.treeView1.Nodes.Add(departmentnode);
                var majors=edubase.tb_Major.Where(c=>c.DepartmentNo==department.No);
                foreach( var major in majors)
                {
                    var majornode=new TreeNode(major.Name);
                    departmentnode.Nodes.Add(majornode);
                    var classes=edubase.tb_Class.Where(c=>c.MajorNo==major.No);
                    foreach(var cls in classes)
                    {
                        var classNode = new TreeNode(cls.Name);
                        classNode.Tag = cls.No;
                        majornode.Nodes.Add(classNode);
                    }

                }
            }
            //var courses=edubase.tb_Course.ToList(); 
            //this.comboBox1.DataSource = courses;
            //this.comboBox1.DisplayMember = "Name";
            //this.comboBox1.ValueMember = "No";
        }

        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {
            if (treeView1.SelectedNode.Level != 2) return;
            this.classsno = (int)this.treeView1.SelectedNode.Tag;
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = "server=(local);database=EduBaseDemo;Integrated Security=False;uid=sa;password=123456";
            sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText = $"SELECT *FROM dbo.tb_Course WHERE ClassNo='{classsno}'";
            SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);
            adapter.SelectCommand = sqlCommand;
            DataTable dataTable = new DataTable();  
            adapter.Fill(dataTable);
            comboBox1.DataSource = dataTable;   
            comboBox1.DisplayMember = "Name";
            comboBox1.ValueMember = "No";

        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (comboBox1.SelectedItem != null)
            {
               if(int.TryParse(this.comboBox1.SelectedValue?.ToString(),out int courseNo))
                {
                    courseno = courseNo;
                }
            }
            else
            {
                courseno=0;
            }
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = "server =(local);database=EduBaseDemo;integrated security=false;uid=sa;password=123456";
            sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand();   
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText = $"SELECT ss.No,ss.Name,s.PinshiScore,s.QimoScore,s.ZongpingScore,s.PinjiaoScore FROM\r\n" +
                $"dbo.tb_Score AS s JOIN dbo.tb_Student AS ss ON ss.No=s.StuNo\r\nWHERE \r\nss.ClassNo='{classsno}' AND s.CourseNo='{courseno}'";
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = sqlCommand;
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            dataGridView1.DataSource = dataTable;
            dataGridView1.Columns["Name"].HeaderText = "学号";
            dataGridView1.Columns["PinshiScore"].HeaderText = "平时";
            dataGridView1.Columns["ZongpingScore"].HeaderText = "总评";
            dataGridView1.Columns["QimoScore"].HeaderText = "期末";
            dataGridView1.Columns["PinjiaoScore"].HeaderText = "评教";
            int less60 = 0;
            int more60 = 0;
            int more70 = 0;
            int more80 = 0;int more90 = 0;
            int totalstudents = 0;double totalscore = 0;
            int havescorestudent = 0;
            foreach (DataRow row in dataTable.Rows)
            {
                totalstudents++;
                if (row["ZongpingScore"] != DBNull.Value)
                {
                    double score = Convert.ToDouble(row["ZongpingScore"]);
                    totalscore += score;
                    if (score < 60) less60++;
                    else if (score >= 60 && score < 70) more60++;
                    else if (score >= 70 & score < 80) more70++;
                    else if(score>=80 &&score<90)more80++;
                    else more90++;  
                    havescorestudent++;
                }

            }
            double averagescore=havescorestudent>0?totalscore/havescorestudent:0;
            label1.Text = "各分数段人数：0-60分" + less60 + "人，60-70分" + more60 + "人，70-80分" + more70 + "人，80-90分" + more80 + "人，90-100分" + more90 + "人";
            label2.Text = "共" + totalstudents + "人，不及格" + less60 + "人，期末平均" + averagescore + "分";

        }

        private void button1_Click(object sender, EventArgs e)
        {
            DataTable dataTable=(DataTable)dataGridView1.DataSource;
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = "server=(local);database=EduBaseDemo;integrated security=false;uid=sa;password=123456";
            sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText = $"UPDATE dbo.tb_Score SET PinshiScore=@PinshiScore,QimoScore=@QimoScore,ZongpingScore=@ZongpingScore" +
                $" WHERE StuNo=@StuNo AND CourseNo=@CourseNo";
            sqlCommand.Parameters.Add("@PinshiScore", SqlDbType.Int, 4, "PinshiScore");
            sqlCommand.Parameters.Add("@QimoScore", SqlDbType.Int, 10, "QimoScore");
            sqlCommand.Parameters.Add("@ZongpingScore", SqlDbType.Int, 10, "ZongpingScore");
            sqlCommand.Parameters.AddWithValue("@CourseNo", courseno);
            sqlCommand.Parameters.Add("@StuNo",SqlDbType.Char,10,"No");
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.UpdateCommand = sqlCommand;
            int rowfected=adapter.Update(dataTable);
            MessageBox.Show($"更新{rowfected}行");
        }
    }
}
